package com.ustadzmenjawab.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.Date;
import java.util.Hashtable;
import java.util.Map;

import com.ustadzmenjawab.util.ArrayOps;
import com.ustadzmenjawab.util.DateConverter;



import twitter4j.HashtagEntity;
import twitter4j.MediaEntity;
import twitter4j.MediaEntity.Size;
import twitter4j.Status;
import twitter4j.URLEntity;
import twitter4j.User;
import twitter4j.UserMentionEntity;

public class DBAccess {
	public Hashtable<Long,String> getUstadzList(){
		Connection conn = DBConnFactory.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			String sql = "SELECT * FROM ustadz_list";
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery(sql);
			Hashtable<Long,String> ustlist = new Hashtable<Long,String>();
			while(rs.next()){
				long userid = rs.getLong(1);
				String screen_name = rs.getString(2);
				ustlist.put(userid, screen_name);
			}
			return ustlist;
		}catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeConnection(rs,ps,conn);
		}
		return null;
	}


	/*public void insertUstadzTweet(Status status){
		Connection conn = DBConnFactory.getConnection();
		String sql = "INSERT INTO public_tweet(statusid" +
		", userid" +
		", screen_name" +
		", text" +
		", in_reply_to_status_id" +
		", in_reply_to_screen_name" +
		", in_reply_to_user_id" +
		", created_at" +
		", retweet_count" +
		", is_favorited" +
		", is_retweet" +
		", is_truncated" +
		", contributors" +
		", in_retweet_to_status_id" +
		", in_retweet_to_user_id" +
		", convid) "
		+" VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

		PreparedStatement ps;
		try {
			ps = conn.prepareStatement(sql);

			ps.setLong(1, status.getId());
			User user = status.getUser();
			if(user == null){
				return;
			}
			ps.setLong(2, user.getId());
			ps.setString(3, status.getUser().getScreenName());
			ps.setString(4,status.getText());
			ps.setLong(5, status.getInReplyToStatusId());
			ps.setString(6, status.getInReplyToScreenName());
			ps.setLong(7, status.getInReplyToUserId());
			ps.setTimestamp(8, status.getCreatedAt() == null ? null : new Timestamp(status.getCreatedAt().getTime()));
			ps.setInt(9, (int)status.getRetweetCount());	
			ps.setBoolean(10, status.isFavorited());
			ps.setBoolean(11, status.isRetweet());
			ps.setBoolean(12,status.isTruncated());
			ps.setString(13, status.getContributors() == null ? null : ArrayOps.implode(status.getContributors(),","));
			ps.setLong(14, status.getRetweetedStatus() == null ? -1 : status.getRetweetedStatus().getId());
			ps.setLong(15,  status.getRetweetedStatus() == null ? -1 : status.getRetweetedStatus().getUser().getId());
			ps.setLong(16, findConversationID(status));
			ps.execute();
		} catch (SQLException e) {
			if(e.getErrorCode() == 1022 || e.getErrorCode() == 1062){
				updateUstadzTweet(status);
			}else{
				e.printStackTrace();
			}
		}  finally{
			closeConnection(conn);
		}
	}*/

	/*public void updateUstadzTweet(Status status){

		Connection conn = DBConnFactory.getConnection();
		String sql = "UPDATE public_tweet SET text = ? , retweet_count = ? WHERE statusid = ?";
		PreparedStatement ps;
		try{
			ps = conn.prepareStatement(sql);
			ps.setString(1, status.getText());
			ps.setLong(2, status.getRetweetCount());
			ps.setLong(3, status.getId());
			ps.execute();

		}catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally{
			closeConnection(conn);
		}
	}*/

	public void updatePublicTweet(Status status) {

		Connection conn = DBConnFactory.getConnection();
		String sql = "UPDATE public_tweet SET text = ?, retweet_count = ? WHERE statusid = ?";
		PreparedStatement ps = null;;
		try{
			ps = conn.prepareStatement(sql);
			ps.setString(1, status.getText());
			ps.setLong(2, status.getRetweetCount());
			ps.setLong(3, status.getId());
			ps.execute();

		}catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}  finally{
			closeConnection(null,ps,conn);
		}
	}

	public long getTweetConvID(long userid, String hashtag){
		Connection conn = DBConnFactory.getConnection();
		String sql = "SELECT statusid FROM tweet_hashtag_entity " +
						"where userid = ? and hashtag_text = ?" +
						" and DATE(created_at) = DATE(?)" +
						" ORDER BY created_at ASC LIMIT 1";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setLong(1,userid);
			ps.setString(2,hashtag);
			ps.setTimestamp(3, new Timestamp(DateConverter.convertToLocalTime(new Date()).getTime()));
			rs = ps.executeQuery();
			while(rs.next()){
				long convid = rs.getLong(1);
				return convid;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			closeConnection(rs,ps,conn);
		}
		return -1;
	}
	public long getTweetConvID(long statusid){
		String[] tblNames ={"public_tweet"}; 
		Connection conn = DBConnFactory.getConnection();
		for(String tblName : tblNames){
			String sql_ustadz = "SELECT convid FROM "+ tblName+ " where statusid = "+statusid;
			Statement stmt = null;
			ResultSet rs = null;
			try {
				stmt = conn.createStatement();
				rs = stmt.executeQuery(sql_ustadz);
				if(rs.next()){
					return rs.getLong(1);
				}
				if(rs != null)
					rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}finally{
				closeConnection(rs,stmt,conn);
			}
		}
	
		return -1;
	}
	public long insertPublicTweet(Status status){
		Connection conn = DBConnFactory.getConnection();
		String sql = "INSERT INTO public_tweet(statusid" +
		", userid" +
		", screen_name" +
		", text" +
		", in_reply_to_status_id" +
		", in_reply_to_screen_name" +
		", in_reply_to_user_id" +
		", created_at" +
		", retweet_count" +
		", is_favorited" +
		", is_retweet" +
		", is_truncated" +
		", contributors" +
		", in_retweet_to_status_id" +
		", in_retweet_to_user_id" +
		", convid) "
		+" VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setLong(1, status.getId());
			User user = status.getUser();
			ps.setLong(2, user.getId());
			ps.setString(3,user.getScreenName() == null ? null : user.getScreenName());
			ps.setString(4,status.getText() == null ? null : status.getText());
			ps.setLong(5,status.getInReplyToStatusId());
			ps.setString(6, status.getInReplyToScreenName());
			ps.setLong(7, status.getInReplyToUserId());
			ps.setTimestamp(8, status.getCreatedAt() == null ? null : new Timestamp(DateConverter.convertToLocalTime(status.getCreatedAt()).getTime()));
			ps.setInt(9, (int)status.getRetweetCount());	
			ps.setBoolean(10, status.isFavorited());
			ps.setBoolean(11, status.isRetweet());
			ps.setBoolean(12,status.isTruncated());
			ps.setString(13,status.getContributors() == null ? null : ArrayOps.implode(status.getContributors(),","));
			ps.setLong(14, status.getRetweetedStatus() == null ? -1 : status.getRetweetedStatus().getId());
			ps.setLong(15,  status.getRetweetedStatus() == null ? -1 : status.getRetweetedStatus().getUser().getId());
			long convid = findConversationID(status);
			ps.setLong(16, convid);
			ps.execute();
			if(status.getInReplyToStatusId() > -1){
				updateConvID(status.getId(), convid);
			}

			return convid;
		} catch (SQLException e) {
			if(e.getErrorCode() == 1022 || e.getErrorCode() == 1062){
				updatePublicTweet(status);
			}else{
				e.printStackTrace();
			}
		}  finally{
			closeConnection(null,ps,conn);
		}
		return -1;
	}

	public void updateConvID(long fromID, long toID){
		String sql = "UPDATE public_tweet SET convid = ? WHERE convid = ?";
		PreparedStatement ps = null;
		Connection conn = DBConnFactory.getConnection();
		try{
			ps = conn.prepareStatement(sql);
			ps.setLong(1, toID);
			ps.setLong(2,fromID);
			ps.execute();

		} catch (SQLException e) {
			e.printStackTrace();	
		}  finally{
			closeConnection(null,ps,conn);
		}	
	}
	public void insertConvTweetSummary(long convid, Status status){
		if(convid == -1)
			return;
		String sql = "INSERT INTO conv_tweet_summary(" +
		"convid" +
		", statusid" +
		", userid" +
		", screen_name" +
		", text" +
		", created_at" +
		", retweet_count" +
		", is_favorited" +
		", is_retweet" +
		", is_truncated" +
		", convid_count)" +
		" VALUES(?,?,?,?,?,?,?,?,?,?,?)";
		PreparedStatement ps = null ;
		Connection conn = DBConnFactory.getConnection();
		try{
			ps = conn.prepareStatement(sql);
			ps.setLong(1, convid);
			ps.setLong(2, status.getId());
			ps.setLong(3, status.getUser().getId());
			ps.setString(4, status.getUser().getScreenName());
			ps.setString(5, status.getText());
			ps.setTimestamp(6, new Timestamp(status.getCreatedAt().getTime()));
			ps.setLong(7, status.getRetweetCount());
			ps.setBoolean(8, status.isFavorited());
			ps.setBoolean(9, status.isRetweet());
			ps.setBoolean(10, status.isTruncated());
			ps.setInt(11, 1);
			ps.execute();
		}catch (SQLException e) {
			if(e.getErrorCode() == 1022 || e.getErrorCode() == 1062){
				updateConvTweetSummary(convid,status);
			}else{
				e.printStackTrace();
			}
		}  finally{
			closeConnection(null,ps,conn);
		}	
	}

	public void updateConvTweetSummary(long convid, Status status){
		if(convid == -1){
			return;
		}
		String sql = "UPDATE conv_tweet_summary SET convid_count = convid_count + 1,retweet_count = ?  WHERE convid = ?";
		PreparedStatement ps = null ;
		Connection conn = DBConnFactory.getConnection();

		try{
			ps = conn.prepareStatement(sql);
			ps.setLong(1, status.getRetweetCount());
			ps.setLong(2, convid);
			ps.execute();
		}catch (SQLException e) {
			e.printStackTrace();	
		}  finally{
			closeConnection(null, ps,conn);
		}	
	}
	public long findConversationID(Status status){
		if(status == null)
			return -1;
		if(status.isRetweet() && status.getRetweetedStatus() != null){
			long retweetedid = status.getRetweetedStatus().getId();
			long convid = getTweetConvID(retweetedid);
			if(convid == -1){
				return retweetedid;
			}
			return convid;
		}else if(status.getInReplyToStatusId() > -1){
			long repliedid = status.getInReplyToStatusId();
			long convid = getTweetConvID(repliedid);
			if(convid == -1){
				return repliedid;
			}
			return convid;
		}else if(status.getHashtagEntities() != null && status.getHashtagEntities().length > 0 ){
			HashtagEntity[] entities = status.getHashtagEntities();
			long convid = -1;
			for(HashtagEntity entity  : entities){
				long temp = getTweetConvID(status.getUser().getId(),entity.getText());
				if(convid == -1 || convid > temp){
					convid = temp;
				}
			}
			if(convid == -1){
				return status.getId();
			}
			return convid;
		}else if(status.getUserMentionEntities() != null && status.getUserMentionEntities().length >0){
			UserMentionEntity[] entities = status.getUserMentionEntities();
			//System.out.println("detectImplicitRetweet");
			for(UserMentionEntity entity : entities){
				long convid = detectImplicitRetweet(entity.getScreenName(),entity.getId(),status.getText(),DateConverter.convertToLocalTime(status.getCreatedAt()));
				/*long mentionById = status.getUser().getId();
				long mentionId = entity.getId();
				long convid = findEarliestMention(mentionId, mentionById, DateConverter.convertToLocalTime(status.getCreatedAt()));
				 */
				if(convid > -1)
					return convid;
			}
			return status.getId();
		}else if(Character.isDigit(status.getText().trim().charAt(0))){
			//System.out.println("detectImplicitRetweet");
			return detectChainedTweet(status);
		}
		return status.getId();
	}

	public long findEarliestMention(long fromUserID, long toUserID,Date date){
		if(date == null)
			return -1;
		String sql = "SELECT * FROM tweet_user_mention_entity " +
		" WHERE ((mentioned_user_id = ? and mentioned_by = ?) OR (mentioned_user_id = ? and mentioned_by = ?))" +
		"and DATE(created_at) = DATE(?) " +
		"ORDER BY created_at DESC LIMIT 1";
		Connection conn = DBConnFactory.getConnection();
		PreparedStatement ps  = null;
		ResultSet rs = null;
		try{
			ps = conn.prepareStatement(sql);
			ps.setLong(1, toUserID);
			ps.setLong(2, fromUserID);
			ps.setLong(3, fromUserID);
			ps.setLong(4, toUserID);
			ps.setTimestamp(5, new Timestamp(date.getTime()));
			rs = ps.executeQuery();
			if(rs.next()){
				long statusid = rs.getLong("statusid");
				rs.close();
				return getTweetConvID(statusid);
			}
			return -1;
		}catch(Exception e){

			e.printStackTrace();
		}finally{
			closeConnection(rs,ps,conn);
		}
		return -1;
	}

	public long detectImplicitRetweet(String mentionedName, long mentionedUserID, String text,Date tweetCreatedAt){
		if(mentionedName == null || text == null)
			return -1;
		System.out.println("detectImplicitRetweet" + mentionedName + "," + text);
		int idx = text.indexOf("@"+mentionedName)+mentionedName.length()+1;
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			String sample = text.substring(idx+2,Math.min(idx+22, text.length()));
			if(sample == null){
				return -1;
			}
			sample = sample.trim();
			String sql = "SELECT convid FROM public_tweet " +
			" WHERE (text like ? OR userid = ?)" +
			" AND text like ? " +
			" AND DATE(created_at) = DATE(?) " +
			" ORDER BY created_at ASC LIMIT 1";
			conn = DBConnFactory.getConnection();

			ps = conn.prepareStatement(sql);
			ps.setString(1, "%@"+mentionedName+"%");
			ps.setLong(2, mentionedUserID);
			ps.setString(3, "%"+sample+"%");
			ps.setTimestamp(4,new Timestamp(DateConverter.convertToLocalTime(new Date()).getTime()));
			rs = ps.executeQuery();
			while(rs.next()){
				long convid = rs.getLong(1);
				return convid;
			}
			return -1;
		}catch(SQLException e){
			e.printStackTrace();
		}catch(StringIndexOutOfBoundsException ex){
			return -1;
		}finally{
			closeConnection(rs,ps,conn);
		}
		return -1;
	}

	public long detectChainedTweet(Status status){
		String text = status.getText().trim();
		if(!Character.isDigit(text.charAt(0))){
			return -1;
		}
		System.out.println("detectChainedTweet " +status.getId()+","+ text);
		String delimiter="";
		long convid = status.getId();
		if(Character.isDigit(text.charAt(1))){
			if(text.substring(2,3).equalsIgnoreCase(".") ){
				delimiter = ".";
			}else if(text.substring(2,3).equalsIgnoreCase(")")){
				delimiter = ")";
			}else if(text.substring(2,3).equalsIgnoreCase(">")){
				delimiter = ">";
			}else if(text.substring(2,3).equalsIgnoreCase("/")){
				delimiter = "/";
			}else if(text.substring(2,3).equalsIgnoreCase("\\")){
				delimiter = "\\";
			}

			if(delimiter.length() > 0){
				convid =  getFirstInChainedTweet(status,delimiter);
			}
		}

		if(text.substring(1, 2).equalsIgnoreCase(".")){
			delimiter = ".";
		}else if(text.substring(1, 2).equalsIgnoreCase(")")){
			delimiter = ")";
		}else if(text.substring(1, 2).equalsIgnoreCase(">")){
			delimiter = ">";
		}else if(text.substring(2,3).equalsIgnoreCase("/")){
			delimiter = "/";
		}else if(text.substring(2,3).equalsIgnoreCase("\\")){
			delimiter = "\\";
		}
		if(delimiter.length() > 0){
			convid =  getFirstInChainedTweet(status,delimiter); 
		}
		return convid;
	}

	public long getFirstInChainedTweet(Status status, String delimiter){
		String sql = "SELECT convid,MIN(TIMEDIFF(?,created_at)) FROM public_tweet " +
		"WHERE text LIKE ? AND  " +
		" userid = ? AND DATE(created_at) = DATE(?)" ;

		Connection conn = DBConnFactory.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			ps = conn.prepareStatement(sql);
			ps.setTimestamp(1,  new Timestamp(DateConverter.convertToLocalTime(status.getCreatedAt()).getTime()));
			ps.setString(2, "1"+delimiter+"%");
			ps.setLong(3, status.getUser().getId());
			ps.setTimestamp(4, new Timestamp(DateConverter.convertToLocalTime(status.getCreatedAt()).getTime()));
			rs = ps.executeQuery();
			while(rs.next()){
				return rs.getLong(1);
			}
			return -1;
		}catch(SQLException e){
			e.printStackTrace();
		} finally{
			closeConnection(rs,ps,conn);
		}
		return -1;
	}
	public void insertUser(User user){
		String sql = "INSERT INTO user_profile(" +
		" userid" +
		", screen_name" +
		", name" +
		", created_at" +
		", description" +
		", location" +
		", profile_img_url" +
		", profile_img_url_https" +
		", language" +
		", follower_count" +
		", friend_count" +
		", favorite_count" +
		", url" +
		", is_protected" +
		", is_translator" +
		", is_verified" +
		", is_geo_enabled" +
		", timezone)"
		+" VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

		Connection conn = DBConnFactory.getConnection();
		PreparedStatement ps = null;
		try{
			ps = conn.prepareStatement(sql);
			ps.setLong(1, user.getId());
			ps.setString(2,user.getScreenName()== null ? null :  user.getScreenName());
			ps.setString(3, user.getName()== null ? null : user.getName());
			ps.setTimestamp(4, user.getCreatedAt()== null ? null : new Timestamp(DateConverter.convertToLocalTime(user.getCreatedAt()).getTime()));
			ps.setString(5, user.getDescription()== null ? null : user.getDescription());
			ps.setString(6, user.getLocation() == null ? null : user.getLocation());
			ps.setString(7, user.getProfileImageURL() == null ? null : user.getProfileImageURL().toString());
			ps.setString(8, user.getProfileBackgroundImageUrlHttps() == null ? null : user.getProfileBackgroundImageUrlHttps().toString());
			ps.setString(9, user.getLang() == null ? null : user.getLang());
			ps.setInt(10,user.getFollowersCount());
			ps.setInt(11, user.getFriendsCount());
			ps.setInt(12, user.getFavouritesCount());
			ps.setString(13,user.getURL() == null ? null : user.getURL().toString());
			ps.setBoolean(14, user.isProtected());
			ps.setBoolean(15, user.isTranslator());
			ps.setBoolean(16, user.isVerified());
			ps.setBoolean(17, user.isGeoEnabled());
			ps.setString(18, user.getTimeZone() == null ? null : user.getTimeZone());

			ps.execute();
		}catch(SQLException e){
			if(e.getErrorCode() == 1022 || e.getErrorCode() == 1062)
				updateUser(user);
			else
				e.printStackTrace();
		} finally{
			closeConnection(null,ps,conn);
		}
	}

	public void updateUser(User user){
		if(user == null || user.getId() < 1)
			return;
		String sql = "UPDATE user_profile " +
		" SET screen_name = ?" +
		", name = ?" +
		", created_at = ?" +
		", description = ?" +
		", location = ?" +
		", profile_img_url = ?" +
		", profile_img_url_https = ?" +
		", language = ?" +
		", follower_count = ?" +
		", friend_count = ?" +
		", favorite_count = ?" +
		", url = ?" +
		", is_protected = ?" +
		", is_translator = ?" +
		", is_verified = ?" +
		", is_geo_enabled = ?" +
		", timezone = ?" +
		" WHERE userid = ?";

		Connection conn = DBConnFactory.getConnection();
		PreparedStatement ps = null;
		try{
			ps = conn.prepareStatement(sql);
			ps.setString(1, user.getScreenName());
			ps.setString(2, user.getName());
			ps.setTimestamp(3, new Timestamp(user.getCreatedAt().getTime()));
			ps.setString(4,user.getDescription() == null ? null : user.getDescription() );
			ps.setString(5, user.getLocation()== null ? null :user.getLocation());
			ps.setString(6, user.getProfileImageURL().toString()== null ? null :user.getProfileImageURL().toString());
			ps.setString(7, user.getProfileImageUrlHttps().toString()== null ? null :user.getProfileImageUrlHttps().toString());
			ps.setString(8, user.getLang()== null ? null :user.getLang());
			ps.setInt(9,user.getFollowersCount());
			ps.setInt(10, user.getFriendsCount());
			ps.setInt(11, user.getFavouritesCount());
			ps.setString(12, user.getURL()== null ? null : user.getURL().toString());
			ps.setBoolean(13, user.isProtected());
			ps.setBoolean(14, user.isTranslator());
			ps.setBoolean(15, user.isVerified());
			ps.setBoolean(16, user.isGeoEnabled());
			ps.setString(17, user.getTimeZone()== null ? null : user.getTimeZone());
			ps.setLong(18, user.getId());
			ps.execute();
		}catch(Exception e){
			e.printStackTrace();
		} finally{
			closeConnection(null,ps,conn);
		}
	}

	public com.ustadzmenjawab.entity.User getUser(long userid){
		String sql = "SELECT * FROM user_profile WHERE userid = ?";
		Connection conn = DBConnFactory.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			ps = conn.prepareStatement(sql);
			ps.setLong(1, userid);
			rs = ps.executeQuery();
			if(rs.next()){
				com.ustadzmenjawab.entity.User user = new com.ustadzmenjawab.entity.User();
				user.setUserid(userid);
				user.setCreatedAt(rs.getTimestamp("created_at"));
				user.setDescription(rs.getString("description"));
				user.setFavoriteCount(rs.getInt("favorite_count"));
				user.setFollowerCount(rs.getInt("follower_count"));
				user.setFriendCount(rs.getInt("friend_count"));
				user.setGeoEnabled(rs.getBoolean("is_geo_enabled"));
				user.setLanguage(rs.getString("language"));
				user.setLocation(rs.getString("location"));
				user.setName(rs.getString("name"));
				user.setProfileImgUrl(rs.getString("profile_img_url"));
				user.setProfileImgUrlHttps(rs.getString("profile_img_url_https"));
				user.setProtected(rs.getBoolean("is_protected"));
				user.setScreenName(rs.getString("screen_name"));
				user.setTimezone(rs.getString("timezone"));
				user.setTranslator(rs.getBoolean("is_translator"));
				user.setUrl(rs.getString("url"));
				user.setVerified(rs.getBoolean("is_verified"));
				return user;
			}
		}catch(Exception e){
			e.printStackTrace();
		} finally{
			closeConnection(rs,ps,conn);
		}
		return null;
	}

	public void deleteStatus(long statusid){
		String sql = "DELETE FROM public_tweet WHERE statusid = ?";

		Connection conn = DBConnFactory.getConnection();
		PreparedStatement ps = null;
		try{
			ps = conn.prepareStatement(sql);
			ps.setLong(1, statusid);
			ps.execute();
			ps.close();

		}catch(Exception e){
			e.printStackTrace();
		} finally{
			closeConnection(null,ps,conn);
		}
	}

	public void insertMediaEntity(long statusid, MediaEntity entity){
		if(entity == null || statusid < 0)
			return;

		String displayURL = entity.getDisplayURL();
		int startInd = entity.getStart();
		int endInd  = entity.getEnd();
		String expandedURL  = entity.getExpandedURL().toExternalForm();
		long mediaid = entity.getId();
		String url = entity.getURL().toExternalForm();
		String mediaURL = entity.getMediaURL().toExternalForm();
		String mediaURLHttps = entity.getMediaURLHttps().toExternalForm();
		Map<Integer,Size> sizes = entity.getSizes();
		Size large = sizes.get(Size.LARGE);
		Size medium = sizes.get(Size.MEDIUM);
		Size small = sizes.get(Size.SMALL);
		Size thumb = sizes.get(Size.THUMB);

		Connection conn = DBConnFactory.getConnection();
		String sql = "INSERT INTO tweet_media_entity(" +
		"statusid" +
		", mediaid" +
		", media_url" +
		", media_url_https" +
		", url" +
		", display_url" +
		", expanded_url" +
		", size_large_w" +
		", size_large_resize" +
		", size_large_height" +
		", size_medium_w" +
		", size_medium_resize" +
		", size_medium_h" +
		", size_small_w" +
		", size_small_resize" +
		", size_small_h" +
		", size_thumb_w" +
		", size_thumb_resize" +
		", size_thumb_h" +
		", index_start" +
		", index_end)" +
		" VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setLong(1, statusid);
			ps.setLong(2, mediaid);
			ps.setString(3, mediaURL);
			ps.setString(4, mediaURLHttps);
			ps.setString(5, url);
			ps.setString(6, displayURL);
			ps.setString(7, expandedURL);
			ps.setInt(8, large.getWidth());
			ps.setInt(9,  large.getResize());
			ps.setInt(10, large.getHeight());
			ps.setInt(11, medium.getWidth());
			ps.setInt(12, medium.getResize());
			ps.setInt(13, medium.getHeight());
			ps.setInt(14, small.getWidth());
			ps.setInt(15, small.getResize());
			ps.setInt(16, small.getHeight());
			ps.setInt(17, thumb.getWidth());
			ps.setInt(18, thumb.getResize());
			ps.setInt(19, thumb.getHeight());
			ps.setInt(20, startInd);
			ps.setInt(21, endInd);

			ps.execute();
		} catch (SQLException e) {
			if(e.getErrorCode() != 1022 && e.getErrorCode() != 1062){
				e.printStackTrace();
			}
		}finally{
			closeConnection(null,ps,conn);
		}

	}

	public void insertURLEntity(long statusid, URLEntity entity){
		if(entity == null || statusid < 0)
			return;
		String sql = "INSERT INTO tweet_url_entity(" +
		"statusid" +
		",  url" +
		", display_url" +
		", expanded_url" +
		", index_start" +
		", index_end)" +
		" VALUES(?,?,?,?,?,?)";
		Connection conn = DBConnFactory.getConnection();
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setLong(1, statusid);
			ps.setString(2, entity.getURL().toExternalForm());
			ps.setString(3, entity.getDisplayURL());
			ps.setString(4, entity.getExpandedURL().toExternalForm());
			ps.setInt(5, entity.getStart());
			ps.setInt(6, entity.getEnd());

			ps.execute();
		} catch (SQLException e) {
			if(e.getErrorCode() != 1022 && e.getErrorCode() != 1062){
				e.printStackTrace();
			}
		}finally{
			closeConnection(null,ps,conn);
		}
	}

	public void insertUserMentionEntity(long statusid, long userid, Date created_at,  UserMentionEntity entity){
		if(entity == null || statusid < 0 || created_at == null)
			return;
		String sql = "INSERT INTO tweet_user_mention_entity (" +
		"statusid" +
		", mentioned_user_id" +
		", mentioned_screen_name" +
		", mentioned_name" +
		", index_start" +
		", index_end" +
		", mentioned_by" +
		", created_at)" +
		" VALUES(?,?,?,?,?,?,?,?)"	;

		Connection conn = DBConnFactory.getConnection();
		PreparedStatement ps = null;
		try{
			ps = conn.prepareStatement(sql);
			ps.setLong(1, statusid);
			ps.setLong(2,entity.getId());
			ps.setString(3,entity.getScreenName());
			ps.setString(4, entity.getName());
			ps.setInt(5, entity.getStart());
			ps.setInt(6, entity.getEnd());
			ps.setLong(7, userid);
			ps.setTimestamp(8, created_at == null ? null : new Timestamp(created_at.getTime()));
			ps.execute();
		}catch (SQLException e) {
			if(e.getErrorCode() != 1022 && e.getErrorCode() != 1062){
				e.printStackTrace();
			}
		}finally{
			closeConnection(null, ps, conn);
		}
	}
	public boolean isUstadz(long userid){
		String sql = "Select * FROM ustadz_list where user_id = ?";
		Connection conn = DBConnFactory.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			ps = conn.prepareStatement(sql);
			ps.setLong(1, userid);
			rs = ps.executeQuery();
			if(rs.next()){
				return true;
			}
			return false;
		}catch (SQLException e) {
			if(e.getErrorCode() != 1022 && e.getErrorCode() != 1062){
				e.printStackTrace();
			}
		}finally{
			closeConnection(rs,ps,conn);
		}
		return false;
	}
	public void insertHashtagEntity(long statusid, long userid, Timestamp created_at, HashtagEntity entity){
		if(entity == null || statusid < 0)
			return;
		String sql = "INSERT INTO tweet_hashtag_entity (" +
		"statusid" +
		", hashtag_text" +
		", userid" +
		", created_at" +
		", index_start" +
		", index_end)" +
		" VALUES (?,?,?,?,?,?)";

		Connection conn = DBConnFactory.getConnection();
		PreparedStatement ps =  null;
		try{
			ps = conn.prepareStatement(sql);
			ps.setLong(1, statusid);
			ps.setString(2, entity.getText());
			ps.setLong(3, userid);
			ps.setTimestamp(4,created_at);
			ps.setInt(5,entity.getStart());
			ps.setInt(6, entity.getEnd());

			ps.execute();
		}catch (SQLException e) {
			if(e.getErrorCode() != 1022 && e.getErrorCode() != 1062){
				e.printStackTrace();
			}
		}finally{
			closeConnection(null,ps,conn);
		}
	}
	public void closeConnection(ResultSet rs, Statement ps, Connection conn){
		try {
			if(rs != null && !rs.isClosed()){
				rs.close();
			}
			if(ps != null && !ps.isClosed()){
				ps.close();
			}
			if(conn != null && !conn.isClosed()){
				conn.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void insertEntities(Status status){
		if(status == null)
			return ;
		if(status.getMediaEntities() != null){
			//Insert media entity
			for(MediaEntity entity : status.getMediaEntities()){
				insertMediaEntity(status.getId(), entity);
			}
		}

		if(status.getURLEntities() != null){
			//insert url entity
			for(URLEntity entity : status.getURLEntities()){
				insertURLEntity(status.getId(), entity);
			}
		}

		if(status.getHashtagEntities() != null){
			//insert hashtag entity
			for(HashtagEntity entity : status.getHashtagEntities()){
				insertHashtagEntity(status.getId()
						, status.getUser().getId()
						, status.getCreatedAt() == null ? null : new Timestamp(status.getCreatedAt().getTime())
				, entity);
			}
		}

		if(status.getUserMentionEntities() != null){
			//insert user mention entity
			for(UserMentionEntity entity : status.getUserMentionEntities()){
				insertUserMentionEntity(status.getId(),status.getUser().getId(), status.getCreatedAt(),entity);
			}
		}
	}

	public void insertRetweet(final Status status){
		if(status.isRetweet() && status.getRetweetedStatus() != null){
			Status retweetedStatus = status.getRetweetedStatus();

			insertPublicTweet(retweetedStatus);

			insertEntities(retweetedStatus);
		}
	}


}
